<?php
include_once('dao_log.php');
include_once('dao_common.php');
include_once('dao_mysql.php');
include_once('dao_sqlhelper.php');
include_once('dao_tables.php');
include_once('../../config/dao.cfg.php');
global $_history_data_fence;
$_history_data_fence = intval(date('Ym',mktime(0,0,0,date('m')-5,1,date('Y')))); //将历史查询映射到历史库服务器（当前服务器仅保存mktime(0,0,0,date('m')-5,1,date('Y'))=6个月数据）
//error_reporting(E_ALL);
class DAO
{
	public $dbconn_name= DBCONN_DEFAULT;//目前默认csjob
	public $b_dbconn_by_sql=DBCONN_BYSQL; //根据sql， 选择dbconn的名字
	//--------------------------------
	public $dbname=DBNAME; //选择数据库,如历史数据库.默认数据库由全局配置指定

	public $table='';
	public $query_limit=200;
	public $db = NULL;
	//--------------------
	public $base_sql='';
	public $where_sql = '';
	public $ex_sql=''  ;

	public $metas_table=array();
	//where条件的meta
	public $metas_where=array();
	//----------------------
	//类常量
	public $change_to_index    = true;
	public $not_change_to_index = false;
	public $type='';

	function __construct()
	{
		class_counter_add($this);
		//$this->init();
	}

	function __destruct()
	{
		class_counter_dec($this);
	}
	function set_conn_name($name)
	{
		$this->dbconn_name = $name;
	}
	function init($dbname='',$table='',$metas_table=NULL,$metas_where=NULL)
	{
		unset($this->db);
		$this->dbname=$dbname; //选择数据库,如历史数据库.默认数据库由全局配置指定
		$this->table=$table;
		$this->query_limit=200;
		//--------------------
		$base_sql='';
		$ex_sql=''  ;
		$this->set_metas_table($metas_table);
		//where条件的meta
		$this->metas_where = $metas_where;
		if($this->metas_where==NULL)
		{
			$this->metas_where=array();
		}
	}
	function debug($msg)
	{
		rtrim($msg);
		$msg.="\r\n";
		DAO_Log::debug($msg);
	}
	function error($msg)
	{
		rtrim($msg);
		$msg.="\r\n";
		DAO_Log::error($msg);
	}
	public function set_metas_table(&$table_meta)
	{
		if($table_meta==NULL)
		{
			$table_meta=array();
		}
		$this->metas_table = $table_meta;
	}


	public function set_where_item($field,$meta_data=array())
	{
		if(sizeof($this->metas_where)==0)
		{
			$this->metas_where=array();
		}
		if(isset($this->metas_table))
		{
			if(array_key_exists($field,$this->metas_table))
			{
				$this->metas_where[$field] = $this->metas_table[$field];
			}
		}
		if(!isset($meta_data))
		{
			return ;
		}
		foreach ($meta_data as $key=>$value)
		{
			$this->metas_where[$field][$key]=$value;
		}
	}


	public function get_type()
	{
		if(strlen($this->type)!=0)
		{
			return $this->type;
		}
		$type =  get_class($this);
		$prefix = '';
		$l_prefix = strlen($prefix);

		if(strncmp($type,$prefix,$l_prefix)==0&&strlen($type)>$l_prefix)
		{
			$type = substr($type,$l_prefix);
		}
		$this->type = $type;
		return $this->type;
	}

	/*由业务对象决定对数据库连接的访问逻辑*/
	//2007-5 读(SELECT)从slave，其他通过master
	public function get_conn_name_by_sql($sql)
	{
		$sql = trim($sql);
		$default = '';
		$query =  'query';
		$show  =  'show';
		$desc  =  'desc';

		$oper = 'select';
		$result =  $default;
		$sql=strtolower($sql);
		if(substr_compare($sql,$oper,0,strlen($oper),false)==0)
		{
			$result= '.'.$query; //从slave读
		}
		if(substr_compare($sql,$show,0,strlen($show),false)==0)
		{
			$result= '.'.$query; //从slave读
		}
		if(substr_compare($sql,$desc,0,strlen($desc),false)==0)
		{
			$result= '.'.$query; //从slave读
		}
		DAO_Log::debug("get_conn_name_by_sql : result= $result");

		return $result;

	}


	//从数据库中获取table定义
	public function desc_table($table_name,&$table_meta,&$msg)
	{

		global $metastr;
		global $metanum;
		$meta_types['tinyint']=$metanum;
		$meta_types['int']=$metanum;

		$sql  = 'desc $table_name';
		$ret = $this->run_sql($sql,$data,$affect_row,$msg);

		if($ret)
		{
			return $ret;
		}

		foreach ($data as $num=>$field)
		{
			$field_name = $field['Field'];
			$table_meta['desc'][$field_name]=$field;
			foreach ($field  as $name=>$value)
			{

				$type_array =explode('(',trim($field['Type']));
				$table_meta['meta'][$field_name]=$meta_types[$type_array[0]];
				if(!isset($table_meta['meta'][$field_name]))
				{
					$table_meta['meta'][$field_name] = $metastr;
				}
				if(sizeof($type_array)>1||strlen($type_array[1])>1)
				{
					//长度,去掉 ')'
					$table_meta['meta'][$field_name]['size'] =substr( $type_array[1],0,strlen($type_array[1])-1);
				}
			}
		}
		return 0;
	}

	public function &get_dbconnection(&$sql_array,&$msg)
	{
		//是否已经初始化
		if(is_object($this->db))
		{  //如果没有打开，则重新初始化
			if(!$this->db->is_open())
			{
				unset($this->db);
				$this->db = NULL;
			}
		}
		//if($this->db==NULL)
		{
			/*连接池名目前采用两个逻辑选择
			1:$this->dbconn_name,由调用者显式指定
			2：如bDBconnBySQL为true,则调用getConnNameBySQL，叠加选择结果,
			*/
			$conn_name = $this->dbconn_name;
			if($this->b_dbconn_by_sql)
			{
				//var_dump($sql_array);
				$conn_name .=$this->get_conn_name_by_sql($sql_array[0]);//通过sql，决定连接名字
			}
			//获取连接
			DAO_Log::debug("dbconnection name = $conn_name");
			$this->db = get_sql_connection($conn_name,$msg);
		}
		//获取连接失败
		if($this->db==NULL)
		{
			$this->error(get_class().":get_sql_connection:error:$msg");
		}
		return $this->db;
	}


	//在同一个库上批量的执行sql
	public function run_sql_batch(&$sql_array,&$data,&$field,&$msg)
	{
		if(sizeof($sql_array)==0)
		{
			$msg = 'no statement';
			$this->error('ready to runsql:'.sizeof($sql_array).' sql items');
			return -1;
		}
		$db = $this->get_dbconnection($sql_array,$msg);
		if($db==NULL)
		{
			return -1;
		}
		//选择数据库
		if(strlen($this->dbname) >0)
		{
			if(strcmp($this->db->dbname,$this->dbname)!=0)
			{
				$this->debug('use database : '. $this->dbname.',in old db,dbname='.$this->db->dbname);
				$ret = $this->db->usedb($this->dbname,$msg);
				if($ret)
				{
					$this->error(get_class().":userdb:error:$msg");
					return $ret;
				}
			}
		}
		$this->debug('ready to runsql:'.sizeof($sql_array).' sql items');
		$ret = $this->db->exec_sql_batch($sql_array,$data,$field,$msg);

		return $ret;
	}

	public function  count_history($sql,$begin,$end,$limit_month,$use_curr_table,&$total,&$msg)
	{
		$data;
		$ret = $this->run_sql_history($sql,$begin,$end,$limit_month,$use_curr_table,$data,$field,$msg);
		if($ret<0)
		{
			DAO_Log::error(__FUNCTION__.": error $msg");
			return $ret;
		}
		$total =0;
		foreach($data as $key=>$value)
		{

			$total+=array_sum(array_values($value));
		}
		return 0;
	}

	public function query_history( $fields,$table,$where_sql,$ex_sql,$begin,$end,$limit_month,$use_curr_table,&$data,&$field,&$msg)
	{
		if(strlen($fields)==0)
		$fields='*';
		get_months_between($months,$begin,$end);
		if($use_curr_table)
		{
			array_unshift($months,array('year'=>0,'mon'=>0)); //增加一个空的记录项，当前库
		}
		if(sizeof($months)==0)
		{
			$msg = "no month between $begin and  $end\r\n";
			DAO_Log::error(__FUNCTION__.':$msg');
			return -1;
		}
		$db = $this->get_dbconnection($msg);
		if($db==NULL){
			return -1;
		}
		//选择数据库
		$base_dbname= $this->dbname;
		DAO_Log::debug("base dbname =$base_dbname");
		$union_sql='';
		$i=0;
		foreach($months as $key=>$value)
		{
			if($value['year']>0)
			{
				$history_dbname = sprintf("%s%04d%02d",$base_dbname,$value['year'],$value['mon']);
			}
			else
			{
				$history_dbname = $base_dbname;
			}
			//用union方式查询多个表
			$single_sql = "select $fields from $history_dbname.$table ";
			//给每个表同样的where语句
			attach_sql($single_sql,$where_sql,NULL);
			if($i>0)
			{
				$union_sql .="\r\nunion\r\n";
			}
			$union_sql.=$single_sql;
			$i++;
		}
		//类似limit ,order这些语句加在最后
		attach_sql($union_sql,$ex_sql);
		$ret = $this->db->exec_sql($union_sql,$data,$field,true,$msg);
		if($ret)
		{
			$msg =  __FUNCTION__.":run sql on db $history_dbname error $msg";
		}
		return $ret;

	}

	/*
	根据时间，在多个历史库表上执行同一个SQL
	对于每个不同的库，发出一次usedb指令，然后才执行sql.
	$limit_month,限制只取多少个月的库。如为0，不限制.如指定了end日期，limit是从end往回数。否则是从begin往前数
	$useCurrDB, currDB是指当前库(没有日期后缀的库）。如t_task表在当前库和历史库都有。其他表在当前库没有.
	如useCurrDB为true,在查询的时候包含当前库。否则，只查带日期后缀的库
	*/
	public function run_sql_history($sql,$begin,$end,$limit_month,$use_curr_table,&$data,&$field,&$msg)
	{
		global $_history_data_fence;
		if(strlen($begin)==0&&strlen($end)==0&&$limit_month>0)
		{
			$end = date('Y-m-d');//如果时间未设置，但limitMonth允许查询大于0个月份的，则从当前月开始回退N各月份
		}
		get_months_between($months,$begin,$end,$limit_month);
		if($use_curr_table)
		{
			array_unshift($months,array('year'=>0,'mon'=>0)); //增加一个空的记录项，当前库
		}
		if(sizeof($months)==0)
		{
			$msg = "no month between $begin and  $end\r\n";
			DAO_Log::error(__FUNCTION__.":$msg");
			return -1;
		}
		$db = $this->get_dbconnection($msg);
		if($db==NULL)
		{
			return -1;
		}
		//选择数据库
		$base_dbname= $this->dbname;
		DAO_Log::debug("base dbname =$base_dbname");
		$is_last_server_history  = 0;
		foreach($months as $key=>$value)
		{
			if($value['year']>0)
			{
				$history_dbname = sprintf("%s%04d%02d",$base_dbname,$value['year'],$value['mon']);
				$db_ym=intval(substr($history_dbname,5,6));
				if($db_ym<$_history_data_fence)
				{
					$is_history_server=1;
				}
				else
				{
					$is_history_server=0;
				}

			}
			else
			{
				$history_dbname = $base_dbname;
				$is_history_server=0;
			}
			if($is_history_server!=$is_last_server_history)
			{
				$is_last_server_history = $is_history_server;
				if($is_history_server)
				{
					$this->dbconn_name=HISTORY_DB;
				}
				else
				{
					$this->dbconn_name=DBCONN_DEFAULT;
				}
				$db = $this->get_dbconnection($msg);
				if($db==NULL)
				{
					return -1;
				}
			}
			DAO_Log::debug("runsql on database :$history_dbname");
			$ret = $this->db->usedb($history_dbname,$msg);
			if($ret)
			{
				$msg = __FUNCTION__.":usedb $history_dbname error $msg";
				break;
			}
			$ret = $this->db->exec_sql($sql,$data,$field,true,$msg);
			if($ret)
			{
				$msg =  __FUNCTION__.":run sql on db $history_dbname error $msg";
				break;
			}
		}
		if($is_last_server_history)
		{
			$this->dbconn_name=DBCONN_DEFAULT;
			$db = $this->get_dbconnection($msg);
		}

		if($ret)
		{
			DAO_Log::error(__FUNCTION__." error: $msg");
		}
		return $ret;
	}
	//----------------------------------------------------
	public function run_sql($sql,&$data,&$field,&$msg)
	{
		$sql_array[]=$sql;

		return    $this->run_sql_batch($sql_array,$data,$field,$msg);

	}

	/*************************************************************
	批量插入新数据
	关键点 ：
	1：根据请求里的第一个data数组，将其中数据依次 values(value1,value2)的子句
	2：拼装时，根据metasInsert里定义的列名和类型，未定义的列不增加，
	3: 定义为必须(metasQuery[field]['must']=1)的值必须存在否则报错
	*/
	public function insert_batch(&$data,$insert_option,&$msg=NULL)
	{
		$msg='OK';
		$sql = trim($this->base_sql);
		if(strlen($sql)==0)
		{
			if(strlen($this->table)==0)
			{
				$msg = __CLASS__.'->'.__METHOD__.':unknown table';
				$this->error($msg);
				return -1;
			}
			$sql = "insert $insert_option into $this->table";
		}
		$field_and_values='';
		//组装insert sql
		$ret = build_insert( $field_and_values,$data,$this->metas_table,$msg);
		if($ret<0||strlen($field_and_values)==0)
		{
			$msg = __CLASS__.'->'.__METHOD__.':no value inserted,check input.'.$msg;
			$this->debug($msg);
			return $ret;
		}
		//sql组合成insert into table (fields) values (values)的格式
		$sql = $sql.' '.$field_and_values;
		//执行sql
		$ret  =$this->run_sql($sql,$data,$field,$msg);
		if($ret==0)
		{
			if(strlen($msg)==0)$msg = 'OK';
		}
		return $ret;
	}
	/*************************************************************
	根据请求插入新数据
	关键点 ：
	1：根据请求里的第一个data数组，将其中数据拼装成(field1,field2) values(value1,value2)的子句
	2：拼装时，根据metasInsert里定义的列名和类型，未定义的列不增加，
	3: 定义为必须(metasQuery[field]['must']=1)的值必须存在否则报错
	*/
	public function insert(&$data,&$msg=NULL)
	{
		$data_array[]=$data;
		return $this->insert_batch($data_array,'',$msg);
	}
	/*******************************************************************
	update操作
	1： 根据metasUpdate选择被Update的字段，组合成 (field1,field2)values(value1,value2)的格式
	2： 根据metasQuery选择Update条件，组合成 where从句，重要的条件必须把其meta[field]['must']设置为1
	*/
	public function update(&$data,&$where_cond,&$msg=NULL)
	{
		$msg='OK';
		$sql = trim($this->base_sql);
		if(strlen($sql)==0)
		{
			if(strlen($this->table)==0)
			{
				$msg = __CLASS__.'->'.__METHOD__.':unknown table';
				$this->error($msg);
				return -1;
			}
			$sql = "update $this->table";
		}
		$field_and_values='';
		$ret = build_update( $field_and_values,$data,$this->metas_table,$msg);
		if($ret<0||strlen($field_and_values)==0)
		{
			$msg = 'no value update,check input.'.$msg;
			return $ret;
		}//组合where从句，where从句有可能为空
		$where='';
		$ret = build_where($where,$this->table,$where_cond,$this->metas_where,$msg);

		if($ret<0)
		{
			$msg = 'no value update,build_where error.'.$msg;
			return $ret;
		}

		//组合完整的sql update table set v1=d1,v2=d2 where v3=d3
		$sql.=' set '.$field_and_values;
		attach_sql($sql,$where,$this->ex_sql);
		if(stripos($sql,'where')===false)//not allow  without where
		{
			$ret = -1;
			$msg = __METHOD__.':action affect the whole table is not allow.';
			$this->debug("$msg;sql=$sql");
			return $ret;
		}
		//执行sql
		$ret  = $this->run_sql($sql,$data,$field,$msg);

		if($ret==0)
		{
			if(strlen($msg)==0)$msg = 'OK';
		}
		return $ret;
	}

	/*******************************************************************************
	delete操作，一般都要求有where从句
	->考虑到数据安全，业务上一般都设计为将记录标记置为失效，而不是直接delete
	*/
	public function del(&$data_where,&$msg=NULL)
	{
		$msg='OK';
		$sql = $this->base_sql;
		if(strlen($sql)==0)
		{
			if(strlen($this->table)==0)
			{
				$msg = __CLASS__.'->'.__METHOD__.':unknown table';
				$this->error($msg);
				return -1;
			}
			$sql = "delete from $this->table";
		}
		$ret = build_where($where,$this->table,$data_where,$this->metas_where,$msg);
		if($ret<0)
		{
			$msg = __METHOD__.':no value delete,build_where error.'.$msg;
			return $ret;
		}
		attach_sql($sql,$where,$this->ex_sql);
		if(stripos($sql,'where')===false)//not allow  without where
		{
			$ret = -1;

			$msg = __METHOD__.':action affect the whole table is not allow.';
			$this->debug("$msg;sql=$sql");
			return $ret;
		}
		$ret = $this->run_sql($sql,$data,$field,$msg);

		if($ret==0)
		{
			if(strlen($msg)==0)$msg = 'OK';
		}
		return $ret;
	}
	/*******************************************************************************
	查询
	*/
	public function  query(&$data_where,&$data,&$msg=NULL)
	{
		$where='';
		$ret=0;
		$sql = $this->base_sql;

		if(strlen($sql) == 0)
		{
			if(strlen($this->table)==0)
			{
				$msg = __CLASS__.'->'.__METHOD__.':unknown table';
				return -1;
			}
			$sql = "select * from $this->table";
		}
		$this->debug("base_sql=$sql");
		//根据查询条件组装where子句
		$ret = build_where($where,$this->table,$data_where,$this->metas_where,$msg);
		if($ret<0)
		{
			$msg = __CLASS__.'->'.__METHOD__.'no value query,build_where error.check input.'.$msg;
			$this->error($msg);
			return $ret;
		}
		if(strlen($this->where_sql) > 0)
		{
			$where .= $this->where_sql;
		}
		//增加where和exSQL
		attach_sql($sql,$where,$this->ex_sql);
		//增加limit
		if($this->query_limit>0&&stripos($sql,'limit')===false)
		{
			$sql.=" limit $this->query_limit";
		}

		//开始查询
		$ret = $this->run_sql($sql,$data,$field,$dbgmsg);
		if($ret)
		{
			$this->error('query error: '.$dbgmsg);
			$msg='query error:'.$dbgmsg;
			$ret=-1;
		}
		else
		{
			if(strlen($msg)==0)$msg = 'OK';
			if(count($data[0])==0)
			{
				$msg='function success,but no record exist for this query';
			}
			$this->debug('query result,total :'.sizeof($resp->data).' records.'.$msg);
		}
		return $ret;
	}


	/*******************************************************************************
	查询
	$date_begin,$date_end:当前数据分库逻辑：按月分库，如果dateBegin,dateEnd跨多个月，则需要跨多个库查询
	跨库查询的方案:
	1:使用union。<==
	2:发出多个查询。

	$query_current_table;
	$queryCurrentTable如为true，则在查询里包含当前库（如果开始时间和结束时间包含了当前月)，当前库不带年月后缀。
	如t_task，在当前月的库里可能需要查询，因为未归档的记录会保持在当前库的t_task表里。
	worklog等表不需要，记录直接写到历史表里。
	*/
	public function  query_in_term(&$data_where,$date_begin,$date_end,$limit_month,$query_current_table,&$data,&$msg=NULL)
	{
		$where='';
		$ret=0;
		$sql = $this->base_sql;
		if(strlen($sql) == 0)
		{
			if(strlen($this->table)==0)
			{
				$msg = __CLASS__.'->'.__METHOD__.':unknown table';
				return -1;
			}
			$sql = "select * from $this->table";
		}
		$this->debug("base_sql=$sql");
		//根据查询条件组装where子句
		$ret = build_where($where,$this->table,$data_where,$this->metas_where,$msg);
		if($ret<0)
		{
			$msg = __CLASS__.'->'.__METHOD__.'no value query,build_where error.check input.'.$msg;
			$this->error($msg);
			return $ret;
		}
		if(strlen($this->where_sql) > 0)
		{
			$where .= $this->where_sql;
		}
		//增加where和exSQL
		attach_sql($sql,$where,$this->ex_sql);
		//增加limit
		if($this->query_limit>0&&stripos($sql,'limit')===false)
		{
			$sql.=' limit $this->query_limit';
		}
		//调用runSQL_History，在多个历史库上执行同一条查询语句
		$ret = $this->run_sql_history($sql,$date_begin,$date_end,$limit_month,$query_current_table,$data,$field,$msg);
		return $ret;

	}
};

function &get_dao($dbconn_name=NULL,$dbname=NULL)
{
	static $dao =NULL;

	if($dao==NULL)
	{
		$dao=new DAO();
	}
	if(isset($dbconn_name))
	{
		unset($dao->db);
		$dao->dbconn_name = $dbconn_name;
	}
	if(isset($dbname))
	{
		unset($dao->db);
		$dao->dbname = $dbname;
	}
	return $dao;
}


if($argc>1)
if($argv)
{
	if($argv[1]=='ut_queryInTerm')
	{
		DAO_Log::set_max();
		$para[f_qq]='35241021';
		$dao = new DAO();
		include_once('dao_tables.php');
		global $t_task;
		$dao->init(DBNAME,'t_task)',$t_task,$t_task);
		$ret = $dao->query_in_term($para,'2007-04-01','2007-06-31',3,1,$result,$msg);
		var_dump($result);
		echo "select result=$ret,$msg\r\n".$dao->db->mysqlError()."\r\n";
	}
	if($argv[1]=='ut_getdao')
	{
		DAO_Log::set_max();
		$ret = get_dao()->run_sql('show databases',$result,$field,$msg);
		var_dump($result);
		echo "select result=$ret,$msg\r\n";
	}

	if($argv[1]=='ut_run_history')
	{
		DAO_Log::set_max();
		$ret = get_dao()->run_sql_history('select * from t_task limit 1','20070401','20070607',0,true,$result,$field,$msg);
		//var_dump($result);
		echo "select result=$ret,$msg,resultcount=".sizeof($result)."\r\n";
		$ret = get_dao()->count_history('select count(*) count from t_task limit 1','200704','200706',0,true,$result,$msg);
		echo "count result=$ret,$msg,total =$result\r\n";
	}
	if($argv[1]=='ut_query_history')
	{
		DAO_Log::set_max();
		$ret = get_dao()->query_history( 'f_id','t_task','where f_contact_method=3','limit 100','20070401','20070607',0,true,$result,$field,$msg);
		var_dump($result);
		echo "count result=$ret,$msg,total =".sizeof($result)."\r\n";
	}

	function metas_to_php($table_name,&$dao=null)
	{
		if($dao==NULL)
		{
			$dao=get_dao();
		}
		$ret = $dao->desc_table($table_name,$table,$msg);
		if($ret)
		{
			echo "desc Table error :$ret,".$msg."\r\n";
			return '';
		}
		$template =
		"<?php
            include_once(\'dao_tables.php\');
            global \$metastr;
            global \$metanum;\n
            global \$$table_name;\n\n";
		var_dump($table);
		foreach($table['meta'] as $field=>$meta)
		{
			if(strlen($field)==NULL||$meta==NULL)
			{
				continue;
			}
			if($meta['type']==TYPE_NUM)
			{
				$meta_def = '$metanum';
			}else{
				$meta_def = '$metastr';
			}
			$fieldstr = "\t\$".$table_name."['$field'] = $meta_def;\n";
			$template.=$fieldstr;
		}
		$template.="
            if (\$argv[1]=='ut_$table_name') {
                var_dump(\$$table_name);
            }\n
            \r?>\r\n";
		return  $template;
	}
	if($argv[1]=='ut_desc_table')
	{
		DAO_Log::set_max();
		$dao  =get_dao($argv[2],$argv[3]);
		$tablename = $argv[4];
		if(strlen($tablename)==0)
		{
			$tablename = 't_task';
		}
		$text = metas_to_php($tablename);
		echo "-----------$tablename----------------\r\n".$text."-----------$tablename----------------\r\n";
		if($argv[3]&&strlen($text))
		{
			$file = $argv[5]."/$tablename".'.php';
			$cmd ="php $file ut_$tablename";
			echo "unit test cmd: $cmd \r\n";
			file_put_contents($file,$text);
			system($cmd,$ret);
		}
	}
}
?>